﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Exam.Model;
using Portal.Core;
using Portal.Web.Utility;

namespace Portal.Exam.Bussiness
{
    public class ExamQuestionBll
    {
        string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public ExamQuestionCls CreateModelByIndex(string SiteId, string ExamRoomStudentId, int QuestionIndex)
        {
            string Query = " select TableExamQuestion.* from TableExamQuestion where frkExamRoomStudentId = '" + ExamRoomStudentId + "' and QuestionIndex=" + QuestionIndex;
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;



            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return null;
            }

            ExamQuestionCls tExamQuestionCls = new ExamQuestionCls();
            tExamQuestionCls.ExamQuestionId = (string)dtResult.Rows[0]["ExamQuestionId"];
            tExamQuestionCls.ExamRoomStudentId = (string)dtResult.Rows[0]["frkExamRoomStudentId"];
            tExamQuestionCls.Title = (string)dtResult.Rows[0]["Title"];
            tExamQuestionCls.Question = "";
            try
            {
                tExamQuestionCls.Question = (string)dtResult.Rows[0]["Question"];
            }
            catch { }
            tExamQuestionCls.Type = (string)dtResult.Rows[0]["Type"];
            try
            {
                tExamQuestionCls.MaxPoint = (int)dtResult.Rows[0]["MaxPoint"];
            }
            catch { }
            try
            {
                tExamQuestionCls.StartMemory = (int)dtResult.Rows[0]["StartMemory"];
            }
            catch { }
            try
            {
                tExamQuestionCls.IsMemory = (int)dtResult.Rows[0]["IsMemory"];
            }
            catch { }
            try
            {
                tExamQuestionCls.SecondHidden = (int)dtResult.Rows[0]["SecondHidden"];
            }
            catch { }
            try
            {
                tExamQuestionCls.CorrectAnswer = (string)dtResult.Rows[0]["CorrectAnswer"];
            }
            catch { }

            try
            {
                tExamQuestionCls.StudentAnswer = (string)dtResult.Rows[0]["StudentAnswer"];
            }
            catch { }

            try
            {
                tExamQuestionCls.MarkedNeedReview = (int)dtResult.Rows[0]["MarkedNeedReview"];
            }
            catch { }

            try
            {
                tExamQuestionCls.SecondTime = (int)dtResult.Rows[0]["SecondTime"];
            }
            catch { }

            try
            {
                tExamQuestionCls.QuestionIndex = (int)dtResult.Rows[0]["QuestionIndex"];
            }
            catch { }

            try
            {
                tExamQuestionCls.AnswerTip = (string)dtResult.Rows[0]["AnswerTip"];
            }
            catch { }

            try
            {
                tExamQuestionCls.TeacherFlashAnswer = (string)dtResult.Rows[0]["TeacherFlashAnswer"];
            }
            catch { }
 
            return tExamQuestionCls;
        }


        public ExamQuestionCls CreateModel(string SiteId, string ExamQuestionId)
        {
            string Query = " select TableExamQuestion.* from TableExamQuestion where ExamQuestionId = '" + ExamQuestionId + "' ";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;

            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return null;
            }

            ExamQuestionCls tExamQuestionCls = new ExamQuestionCls();
            tExamQuestionCls.ExamQuestionId = (string)dtResult.Rows[0]["ExamQuestionId"];
            tExamQuestionCls.ExamRoomStudentId = (string)dtResult.Rows[0]["frkExamRoomStudentId"];
            tExamQuestionCls.Title = (string)dtResult.Rows[0]["Title"];
            tExamQuestionCls.Type = (string)dtResult.Rows[0]["Type"];
            try
            {
                tExamQuestionCls.MaxPoint = (int)dtResult.Rows[0]["MaxPoint"];
            }
            catch { }

            try
            {
                tExamQuestionCls.CorrectAnswer = (string)dtResult.Rows[0]["CorrectAnswer"];
            }
            catch { }

            try
            {
                tExamQuestionCls.StudentAnswer = (string)dtResult.Rows[0]["StudentAnswer"];
            }
            catch { }

            try
            {
                tExamQuestionCls.MarkedNeedReview = (int)dtResult.Rows[0]["MarkedNeedReview"];
            }
            catch { }

            try
            {
                tExamQuestionCls.SecondTime = (int)dtResult.Rows[0]["SecondTime"];
            }
            catch { }

            try
            {
                tExamQuestionCls.QuestionIndex = (int)dtResult.Rows[0]["QuestionIndex"];
            }
            catch { }

            try
            {
                tExamQuestionCls.AnswerTip = (string)dtResult.Rows[0]["AnswerTip"];
            }
            catch { }

            try
            {
                tExamQuestionCls.TeacherFlashAnswer = (string)dtResult.Rows[0]["TeacherFlashAnswer"];
            }
            catch { }
 

            return tExamQuestionCls;
        }


        public DataTable ReadingExamAnswer(string SiteId, string ExamQuestionId)
        {
            DataTable dataTable = new DataTable();
            
            //string[] orderBys = new string[] { "IndexOrder", "ExamAnswerId", "AnswerTextFirst" };
            //Random rnd = new Random();
            //int orderValue = rnd.Next(0, 3);

            string Query = "select * from TableExamAnswer where frkExamQuestionId='" + ExamQuestionId + "' order by [IndexOrder]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamAnswerByAnswerTextLast(string SiteId, string ExamQuestionId)
        {
            DataTable dataTable = new DataTable();
            string Query = "select * from TableExamAnswer where frkExamQuestionId='" + ExamQuestionId + "' order by [AnswerTextLast]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingExamQuestionForLargeFreeText(string SiteId,string ExamRoomStudentId)
        {
            DataTable dataTable = new DataTable();
            string Query = "select * from TableExamQuestion where [Type]='LargeFreeText' and frkExamRoomStudentId='" + ExamRoomStudentId + "' order by [EntryDate] DESC";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingExamQuestion(string SiteId, string ExamRoomStudentId)
        {
            DataTable dataTable = new DataTable();
            string Query = "select * from TableExamQuestion where [Type]<>'LargeFreeText' and frkExamRoomStudentId='" + ExamRoomStudentId + "' order by QuestionIndex";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamAnswerCorrectForMultiChoice(string SiteId, string ExamQuestionId)
        {
            DataTable dataTable = new DataTable();
            string Query = "select * from TableExamAnswer where CorrectAnswer='YES' and frkExamQuestionId='" + ExamQuestionId + "'";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            da.Fill(dataTable);
            return dataTable;
        }

        public void SaveAnswerYesNo(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string ExamQuestionId,
            int YesNo)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamQuestion set CorrectAnswer=0,StudentAnswer=@StudentAnswer where ExamQuestionId=@ExamQuestionId;" +
                           "update TableExamQuestion set CorrectAnswer=1 where ExamQuestionId=@ExamQuestionId and TableExamQuestion.StudentAnswer=(select top 1 TableExamAnswer.CorrectAnswer from TableExamAnswer where ExamQuestionId=@ExamQuestionId);";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
        
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@StudentAnswer"].Value = YesNo == 0 ? "NO" : "YES";

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SavePoint(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string ExamRoomStudentId,
            string ExamQuestionId,
            int    Point)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableExamQuestion set MaxPoint=@MaxPoint where ExamQuestionId=@ExamQuestionId;" +
                           " update TableExamRoomStudent set DonePoint = (select sum(MaxPoint) from TableExamQuestion where CorrectAnswer=1 and ExamRoomStudentId=@frkExamRoomStudentId) where ExamRoomStudentId=@ExamRoomStudentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;

            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@ExamRoomStudentId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamRoomStudentId"].Value = ExamRoomStudentId;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = Point;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SaveCrossLinkAnswer(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamQuestionId,
            string StudentAnswer,
            string StudentAnswerKeyValue
            )
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            int CorrectAnswer = 1;

            string[] ExamAnswerIds = StudentAnswerKeyValue.Split(new char[] { '|' });
            for (int i = 0; i < ExamAnswerIds.Length; i++)
            {
                if (ExamAnswerIds[i].IndexOf(":") != -1)
                {
                    string[] ExamAnswerIdPAs = ExamAnswerIds[i].Split(new char[] { ':' });
                    if(ExamAnswerIdPAs.Length > 0){
                        if (!ExamAnswerIdPAs[0].ToLower().Equals(ExamAnswerIdPAs[1].ToLower()))
                        {
                            CorrectAnswer = 0;
                            break;
                        }
                    }
                }
            }
                        

            string Query = "Update TableExamQuestion set CorrectAnswer=@CorrectAnswer,StudentAnswer=@StudentAnswer where ExamQuestionId=@ExamQuestionId;";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@StudentAnswer"].Value = StudentAnswer;

            TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@CorrectAnswer"].Value = CorrectAnswer;            

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void SaveBestAnswer(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string ExamQuestionId,
            string ExamAnswerId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamQuestion set CorrectAnswer=0,StudentAnswer=@StudentAnswer where ExamQuestionId=@ExamQuestionId;" +
                           "Update TableExamQuestion set CorrectAnswer=1 where ExamQuestionId=@ExamQuestionId and TableExamQuestion.StudentAnswer=(select top 1 TableExamAnswer.ExamAnswerId from TableExamAnswer where TableExamAnswer.CorrectAnswer='YES' and frkExamQuestionId=@ExamQuestionId);";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@StudentAnswer"].Value = ExamAnswerId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        bool CheckCorrect(string sFlashAnswer,string CorrectFlashAnswer)
        {
            CorrectFlashAnswer = CorrectFlashAnswer.ToUpper().Trim();
            sFlashAnswer = sFlashAnswer.ToUpper().Trim();
            string[] ItemCorrectFlashAnswers = CorrectFlashAnswer.Split(new char[] { ',', ';' },StringSplitOptions.RemoveEmptyEntries);
            string[] ItemFlashAnswer = sFlashAnswer.Split(new char[] { ',', ';' },StringSplitOptions.RemoveEmptyEntries);
            for (int iIndex = 0; iIndex < ItemFlashAnswer.Length; iIndex++)
            {
                bool Found = false;
                for (int iCheck = 0; iCheck < ItemCorrectFlashAnswers.Length; iCheck++)
                {
                    if (ItemFlashAnswer[iIndex].Equals(ItemCorrectFlashAnswers[iCheck]))
                    {
                        Found = true;
                    }
                }
                if (!Found)
                {
                    return false;
                }
            }
            return true;
        }

        public void SaveFlashAnswer(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string ExamQuestionId,
            string FlashAnswer,
            string FlashCorrectAnswer)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamQuestion set CorrectAnswer=0,StudentAnswer=@StudentAnswer where ExamQuestionId=@ExamQuestionId;";
            bool Correct = CheckCorrect(FlashAnswer, FlashCorrectAnswer);
            Query+=
                "Update TableExamQuestion set CorrectAnswer="+(Correct?"1":"0")+" where ExamQuestionId=@ExamQuestionId;";
        
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@StudentAnswer"].Value = FlashAnswer;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void SaveMultiChoice(
                SqlTransaction _SqlTransaction,
                string SiteId, 
                string ExamQuestionId,
                string StudentAnswer)
        {
            DataTable dtAns = ReadingExamAnswerCorrectForMultiChoice(SiteId, ExamQuestionId);
            string[] All = StudentAnswer.Split('|');
            int CorrectNum = 0;
            bool Match = true;
            for (int iIndex = 0; iIndex < All.Length; iIndex++)
            {
                if (All[iIndex].Trim().Length > 0)
                {
                    DataView dv = new DataView(dtAns, "ExamAnswerId='" + All[iIndex] + "'", "", DataViewRowState.CurrentRows);
                    if (dv.Count == 0)
                    {
                        Match = false;
                        break;
                    }
                    else {
                        CorrectNum++;
                    }
                }
            }

            if (CorrectNum != dtAns.Rows.Count)
                Match = false;

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamQuestion set CorrectAnswer=0,StudentAnswer=@StudentAnswer where ExamQuestionId=@ExamQuestionId;";
            if (Match)
            {
                Query +=
                           "Update TableExamQuestion set CorrectAnswer=1 where ExamQuestionId=@ExamQuestionId;";
            }
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@StudentAnswer"].Value = StudentAnswer;

            

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void SaveTrueFalse(
                SqlTransaction _SqlTransaction,
                string SiteId,
                string ExamQuestionId,
                string StudentAnswer)
        {            
            if (!string.IsNullOrEmpty(StudentAnswer) && StudentAnswer.Length > 0)
            {
                DataTable dtAns = ReadingExamAnswerCorrectForMultiChoice(SiteId, ExamQuestionId);
                bool Match = true;
                string[] All = StudentAnswer.Split('|');
                int CorrectNum = 0;
                for (int iIndex = 0; iIndex < All.Length; iIndex++)
                {
                    if (All[iIndex].Trim().Length > 0)
                    {
                        DataView dv = new DataView(dtAns, "ExamAnswerId='" + All[iIndex] + "'", "", DataViewRowState.CurrentRows);
                        if (dv.Count == 0)
                        {
                            Match = false;
                            break;
                        }
                        else
                        {
                            CorrectNum++;
                        }
                    }
                }
                if (CorrectNum != dtAns.Rows.Count)
                    Match = false;

                SqlConnection SqlConnection;
                bool HasParentTransaction = false;

                if (_SqlTransaction == null)
                {
                    SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                    SqlConnection.Open();
                    _SqlTransaction = SqlConnection.BeginTransaction();
                }
                else
                {
                    HasParentTransaction = true;
                }

                string Query = "Update TableExamQuestion set CorrectAnswer=0,StudentAnswer=@StudentAnswer where ExamQuestionId=@ExamQuestionId;";
                if (Match)
                {
                    Query +=
                               "Update TableExamQuestion set CorrectAnswer=1 where ExamQuestionId=@ExamQuestionId;";
                }
                SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
                TempSqlCommand.Transaction = _SqlTransaction;
                TempSqlCommand.CommandText = Query;
                TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

                TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@StudentAnswer"].Value = StudentAnswer;



                try
                {
                    TempSqlCommand.ExecuteNonQuery();
                    if (HasParentTransaction == false)
                    {
                        _SqlTransaction.Commit();
                    }
                }
                catch (Exception ex)
                {
                    if (HasParentTransaction == false)
                    {
                        _SqlTransaction.Rollback();
                    }
                    throw (ex);
                }

                if (HasParentTransaction == false)
                {
                    TempSqlCommand.Connection.Close();
                }
            }
        }


        public void SaveFreeText(
            SqlTransaction _SqlTransaction,
            string SiteId, 
            string ExamQuestionId,
            string StudentAnswer)
        {
            ExamQuestionCls ExamQuestionCls = new ExamQuestionBll().CreateModel(SiteId, ExamQuestionId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamQuestion set StudentAnswer=@StudentAnswer,CorrectAnswer=@CorrectAnswer where ExamQuestionId=@ExamQuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.NText);
            TempSqlCommand.Parameters["@StudentAnswer"].Value = StudentAnswer;

            int CorrectAnswer = 0;
            if (StudentAnswer.ToLower().Equals(ExamQuestionCls.AnswerTip.ToLower()))
            {
                CorrectAnswer = 1;
            }
            TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectAnswer"].Value = CorrectAnswer;



            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }




        public void AddQuestionToExamQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamRoomStudentId,
            string QuestionId)
        {
            //ExamPersonalSessionCls ExamPersonalSessionCls = new ExamPersonalSessionBll().CreateModel(SiteId, QuestionId);
            QuestionCls QuestionCls=new QuestionBll().CreateModel(SiteId, QuestionId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableExamQuestion(ExamQuestionId,frkExamRoomStudentId,Title,Question,Type,MaxPoint,CorrectAnswer,SecondTime,QuestionIndex,AnswerTip)values(@ExamQuestionId,@frkExamRoomStudentId,@Title,@Question,@Type,@MaxPoint,@CorrectAnswer,@SecondTime,@QuestionIndex,@AnswerTip);";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = QuestionId;

            TempSqlCommand.Parameters.Add("@frkExamRoomStudentId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@frkExamRoomStudentId"].Value = ExamRoomStudentId;

            
            TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@Title"].Value = QuestionCls.Title;

            TempSqlCommand.Parameters.Add("@Question", SqlDbType.NText);
            TempSqlCommand.Parameters["@Question"].Value = QuestionCls.Question;

            TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@Type"].Value = QuestionCls.Type;

            TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxPoint"].Value = QuestionCls.MaxPoint;

            TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.Int);
            TempSqlCommand.Parameters["@CorrectAnswer"].Value = QuestionCls.CorrectOrder;

            TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@SecondTime"].Value = QuestionCls.SecondTime;

            TempSqlCommand.Parameters.Add("@QuestionIndex", SqlDbType.Int);
            TempSqlCommand.Parameters["@QuestionIndex"].Value = -1;

            TempSqlCommand.Parameters.Add("@AnswerTip", SqlDbType.NText);
            TempSqlCommand.Parameters["@AnswerTip"].Value = QuestionCls.AnswerTip;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void RemoveQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamQuestionId)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Delete from TableExamQuestion where ExamQuestionId=@ExamQuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void SaveCommentQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamCode, 
            string ExamQuestionId, 
            string Comment, 
            int TeacherPoint, 
            string IndexTeacher)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "update  TableExamQuestion set [Teacher" + IndexTeacher + "]=@Comment,[TeacherPoint"+IndexTeacher+"]=@TeacherPoint where ExamQuestionId=@ExamQuestionId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;
        
            TempSqlCommand.Parameters.Add("@Comment", SqlDbType.NText);
            TempSqlCommand.Parameters["@Comment"].Value = Comment;

            TempSqlCommand.Parameters.Add("@TeacherPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@TeacherPoint"].Value = TeacherPoint;



            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void StartHidden(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamQuestionId
        )
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamQuestion set StartMemory=1 where ExamQuestionId=@ExamQuestionId;";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamQuestionId"].Value = ExamQuestionId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


    }
}
